﻿using Coldairarrow.Business.TD;
using Coldairarrow.Entity.Base;
using Coldairarrow.Entity.Base_Manage;
using Coldairarrow.Entity.DTO;
using Coldairarrow.Entity.IT;
using Coldairarrow.Entity.PB;
using Coldairarrow.Entity.TD;
using Coldairarrow.IBusiness;
using Coldairarrow.Util;
using EFCore.Sharding;
using LinqKit;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace Coldairarrow.Business.PB
{
    public partial class PB_PickUpMainBusiness : BaseBusiness<PB_PickUpMain>, IPB_PickUpMainBusiness, ITransientDependency
    {
        readonly IOperator _operator;
        public PB_PickUpMainBusiness(IDbAccessor db, IOperator @operator)
            : base(db)
        {
            _operator = @operator;
        }

        #region 外部接口

        public async Task<PageResult<PB_PickUpMain>> GetDataListAsync(PageInput<List<ConditionDTO>> input)
        {
            var q = GetIQueryable();
            var where = LinqHelper.True<PB_PickUpMain>();

            //筛选
            //if (!search.Condition.IsNullOrEmpty() && !search.Keyword.IsNullOrEmpty())
            //{
            //    var newWhere = DynamicExpressionParser.ParseLambda<PB_PickUpMain, bool>(
            //        ParsingConfig.Default, false, $@"{search.Condition}.Contains(@0)", search.Keyword);
            //    where = where.And(newWhere);
            //}
            if (input.Search.Count() > 0)
            {
                foreach (var item in input.Search)
                {
                    if (!item.Condition.IsNullOrEmpty() && !item.Keyword.IsNullOrEmpty())
                    {
                        var newWhere = DynamicExpressionParser.ParseLambda<PB_PickUpMain, bool>(
                          ParsingConfig.Default, false, $@"{item.Condition}.Contains(@0)", item.Keyword);
                        where = where.And(newWhere);
                    }

                }
            }

            return await q.Where(where).GetPageResultAsync(input);
        }

        public async Task<PB_PickUpMain> GetTheDataAsync(string id)
        {
            return await GetEntityAsync(id);
        }

        public async Task AddDataAsync(PB_PickUpMain data)
        {
            await InsertAsync(data);
        }

        public async Task UpdateDataAsync(PB_PickUpMain data)
        {
            await UpdateAsync(data);
        }

        [DataDeleteLog(UserLogType.备货管理, "Id", "备货管理")]
        public async Task DeleteDataAsync(List<string> ids)
        {
            string sqlMain = string.Empty;
            //查询备货单明细信息
            List<PB_PickUpList> PickUpList = await Db.GetIQueryable<PB_PickUpList>().Where(x => ids.Contains(x.ParentId)).ToListAsync();
            var picknums = PickUpList.Where(x => x.ReadyNums > 0).ToList();
            if (picknums.Count > 0)
            {
                throw new BusException("删除的备货单中有已备货的不能删除");
            }
            else
            {
                #region 清理占用数量
                //查询库存汇总信息
                List<IT_LocalMaterial> LocalMaterialList = await Db.GetIQueryable<IT_LocalMaterial>().ToListAsync();
                foreach (var item in PickUpList)
                {
                    var itemdata = LocalMaterialList.Where(x => x.LocalId == item.LocationId && x.MaterialId == item.MaterialId).FirstOrDefault();
                    if (itemdata != null)
                    {
                        if (item.StockVolume<= itemdata.Nums_R) {
                            sqlMain += @" update IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " where Id='" + itemdata.Id + "'; ";
                        }
                       
                    }
                }

                #endregion
                List<PB_PickUpMain> PickUpMain = await Db.GetIQueryable<PB_PickUpMain>().Where(x => ids.Contains(x.Id) && x.AssociatedDelivery != null).ToListAsync();
                var AssociatedDeliverydata = PickUpMain.GroupBy(x => new { x.AssociatedDelivery }).Select(x => new { AssociatedDelivery = x.Key.AssociatedDelivery }).ToList();
                foreach (var item in AssociatedDeliverydata)
                {
                    sqlMain += @" Update PB_DeliveryPlanDetails  set States=2  WHERE Id='" + item + "'; ";
                }
                sqlMain += @" DELETE FROM PB_PickUpList WHERE ParentId in ('" + string.Join("','", ids.ToArray()) + "'); ";
                sqlMain += @" DELETE FROM PB_PickUpMain WHERE Id in ('" + string.Join("','", ids.ToArray()) + "'); ";
            }


            var pickuplist = await Db.GetIQueryable<PB_PickListSteps>().Where(m => ids.Contains(m.ParentId)).ToListAsync();
            if (pickuplist.Count > 0)
            {

                foreach (var items in pickuplist)
                {
                    string stepindex = items.StepIndex + "Ready";
                   sqlMain+= "update  PB_DesignDetail set " + stepindex + "= IFNULL(" + stepindex + ", 0)-" + items.StockVolume + " where MaterialId='" + items.MaterialId + "' AND Id='" + items.SubId + "' ;";
                   
                }
               sqlMain+= "DELETE FROM PB_PickListSteps WHERE ParentId in (" + String.Join(", ", ids).Replace("\"", "'") + ") ;";
            }

            await ExecuteSqlAsync(sqlMain);
        }

        /// <summary>
        /// 备货单数据
        /// </summary>
        /// <param name="ParentId"></param>
        /// <returns></returns>
        public async Task<List<PB_PickUpMaininfo>> GetDataListInfo(string ParentId)
        {
            Expression<Func<PB_PickUpMain, PB_Customer, PB_DeliveryNoticeItem, PB_DeliveryNotice, PB_Project, PB_PickUpMaininfo>> select = (a, b, c, d, e) => new PB_PickUpMaininfo
            {

                CustomerName = b.Name,
                CustomerPhone = b.Phone,
                Project = e.Name,
                BuildingNo = c.BuildingNo,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join b in Db.GetIQueryable<PB_Customer>() on a.CusID equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_DeliveryNoticeItem>() on a.ParentId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_DeliveryNotice>() on c.ParentId equals d.Id into cd
                    from d in cd.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Project>() on d.ProjectId equals e.Id into de
                    from e in de.DefaultIfEmpty()
                    select @select.Invoke(a, b, c, d, e);
            List<PB_PickUpMaininfo> listdata = new List<PB_PickUpMaininfo>();
            var qdata = await q.Where(x => x.Id == ParentId).ToListAsync();
            foreach (var item in qdata)
            {
                Expression<Func<PB_PickUpList, PB_Material, Base_Department, PB_Location, PB_Rack, PB_StorArea, PB_DesignDetail, PB_StockUpDTO>> selects = (a, b, c, d, e, f, h) => new PB_StockUpDTO
                {
                    MaterialName = b.Name,
                    MaterialCode = b.Code,
                    AreaIdName = f.Name,
                    RackIdName = e.Name,
                    LocationIdName = d.Name,
                    Nums = h.RealNums,
                    Spec = b.Spec,
                };

                selects = selects.BuildExtendSelectExpre();
                var k = from a in Db.GetIQueryable<PB_PickUpList>().AsExpandable()
                        join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                        from b in ab.DefaultIfEmpty()
                        join c in Db.GetIQueryable<Base_Department>() on a.AreaId equals c.Id into ac
                        from c in ac.DefaultIfEmpty()
                        join d in Db.GetIQueryable<PB_Location>() on a.LocationId equals d.Id into ad
                        from d in ad.DefaultIfEmpty()
                        join e in Db.GetIQueryable<PB_Rack>() on d.RackId equals e.Id into de
                        from e in de.DefaultIfEmpty()
                        join f in Db.GetIQueryable<PB_StorArea>() on d.AreaId equals f.Id into df
                        from f in df.DefaultIfEmpty()
                        join h in Db.GetIQueryable<PB_DesignDetail>() on a.SubId equals h.Id into ah
                        from h in ah.DefaultIfEmpty()
                        select @selects.Invoke(a, b, c, d, e, f, h);
                k = k.Where(x => x.OddNumber == item.Id);
                var res = await k.ToListAsync();

                PB_PickUpMaininfo pblist = new PB_PickUpMaininfo();
                pblist.Id = item.Id;
                pblist.QrCode = item.QrCode;
                pblist.DeptIdName = item.DeptIdName;
                pblist.CreatorId = item.CreatorId;
                pblist.CustomerName = item.CustomerName;
                pblist.CreateTime = item.CreateTime;
                pblist.Deleted = item.Deleted;
                pblist.ParentId = item.ParentId;
                pblist.CustomerName = item.CustomerName;
                pblist.Memo = item.Memo;
                pblist.CusID = item.CusID;
                pblist.CustomerAddress = item.CustomerAddress;
                pblist.CustomerPhone = item.CustomerPhone;
                pblist.Subdata = res;
                pblist.DeliveryTime = item.DeliveryTime;
                pblist.Project = item.Project;
                pblist.StyleName = item.StyleName;
                pblist.BuildingNo = item.BuildingNo;
                listdata.Add(pblist);
            }
            if (listdata.Count > 0)
            {
                return listdata;
            }
            else
            {
                throw new BusException("暂无数据");
            }
            throw new NotImplementedException();
        }
        /// <summary>
        /// 得到最新5条备货单信息
        /// </summary>
        /// <returns>5条备货单信息</returns>
        public async Task<List<PB_PickUpMainDataListDTO>> GetPickUpMainDataList()
        {
            Expression<Func<PB_TransportBillDetail,PB_PickUpMain,PB_DeliveryNoticeItem,PB_DesignList ,PB_Project,PB_Buildingnumber,PB_PickUpMainDataListDTO>> select = (a, b,d,e,f,g) => new PB_PickUpMainDataListDTO
            {
                ProjectName = f.Name,
                BuildingNum = g.Buildingnumber,
                PickUpMainId = b.Id,
                UpdateTime = b.UpdateTime
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in Db.GetIQueryable<PB_TransportBillDetail>().AsExpandable()
                    join b in Db.GetIQueryable<PB_PickUpMain>() on a.PreparationNoteId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_DeliveryNoticeItem>() on b.ParentId equals d.Id into bd
                    from d in bd.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_DesignList>() on d.DesignId equals e.Id into de
                    from e in de.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_Project>() on e.ProjectId equals f.Id into ef
                    from f in ef.DefaultIfEmpty()
                    join g in Db.GetIQueryable<PB_Buildingnumber>() on e.BuildingNumberID equals g.Id into eg
                    from g in eg.DefaultIfEmpty()
                    select @select.Invoke(a, b,d,e,f,g);
            q = q.Distinct().OrderByDescending(x => x.UpdateTime).Take(6);
            var res = await q.ToListAsync();
            if (res != null)
            {
                return res;
            }
            else
            {
                throw new BusException("暂无数据");
            }
        }

        /// <summary>
        /// 装车验证
        /// </summary>
        /// <returns></returns>
        public async Task<PB_PickUpListTansportBillDTO> GetTranspotBillByPickUpListId(PageInput<ConditionDTO> input)
        {
            var res = new PB_PickUpListTansportBillDTO();
            

            string strSQL = @"SELECT project.`Name` AS ProjectName, PB_Buildingnumber.Buildingnumber AS BuildingNum,pm.Id AS PickUpMainId,pm.UpdateTime  FROM PB_PickUpMain pm
	LEFT JOIN PB_DeliveryNoticeItem AS item ON pm.ParentId = item.Id
	LEFT JOIN PB_DesignList AS list ON item.DesignId = list.Id
	LEFT JOIN PB_Project AS project ON list.ProjectId = project.Id
	LEFT JOIN PB_Buildingnumber AS PB_Buildingnumber ON list.BuildingNumberID = PB_Buildingnumber.Id  WHERE
	pm.Id = '" + input.Search.Condition + "'";
            res.PickUpMain = Db.GetListBySqlAsync<PB_PickUpMainDataListDTO>(strSQL).Result.FirstOrDefault();
            strSQL = @"SELECT 
pl.ParentId AS PickUpMainId,	m.Id AS PickUpListId,m.`Code`,m.`Name` AS MaterName,m.Spec,IFNULL(SUM(pl.ReadyNums),0) ReadyNums
FROM PB_PickUpList pl
JOIN PB_Material m ON pl.MaterialId=m.Id
WHERE 1=1 AND pl.ParentId='" + input.Search.Condition + @"' GROUP BY  pl.ParentId,m.Id,	m.`Code`,m.`Name`,m.Spec   ";
            res.Data = Db.GetListBySqlAsync<PickUpListData>(strSQL).Result.GetPageResult<PickUpListData>(input);
            if (res.Data == null)
            {
                throw new BusException("暂无数据");
            }
            res.PlateNOs = new List<string>();
            res.Data.Data.ForEach(aData =>
            {
                strSQL = @"SELECT *
FROM
(SELECT 
td.PreparationNoteId AS PickUpListId,tb.Vehicle AS VehicleId,tb.PlateNO,tb.SendTime,td.MaterialId,tb.IntoTime,SUM(IFNULL(td.PackNums,0)) AS PackNums
FROM PB_TransportBillDetail td
JOIN PB_TransportBill tb ON td.ParentId=tb.Id
GROUP BY td.PreparationNoteId,td.MaterialId,tb.Vehicle,tb.PlateNO,tb.SendTime,tb.IntoTime) t
WHERE 1=1 AND t.PickUpListId='";
                strSQL += input.Search.Condition+ "' and t.MaterialId='"+ aData .PickUpListId + "'";
                aData.Sub = Db.GetListBySqlAsync<TransportBillData>(strSQL).Result;
                aData.Sub.ForEach(bData =>
                {
                    bData.SendTime = bData.SendTime == null ? "" : Convert.ToDateTime(bData.SendTime).ToString("yyyy-MM-dd HH:mm:ss");
                    bData.IntoTime = bData.IntoTime == null ? "" : Convert.ToDateTime(bData.IntoTime).ToString("yyyy-MM-dd HH:mm:ss");
                    string plateNo = bData.PlateNO + (bData.SendTime == null ? "" : "_" + Convert.ToDateTime(bData.SendTime).ToString("yyyy-MM-dd HH:mm:ss"));
                    if (!res.PlateNOs.Contains(plateNo))
                    {
                        res.PlateNOs.Add(plateNo);
                    }
                });
            });
            return res;
        }
        /// <summary>
        /// 打印材料单
        /// </summary>
        /// <param name="ParentId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<PB_PickUpMainLoading> GetDataListLoadingInfo(string ParentId, string id)
        {
            Expression<Func<PB_PickUpMain, PB_DeliveryNoticeItem, PB_DesignList, PB_PickUpMainLoading>> select = (a, c, d) => new PB_PickUpMainLoading
            {
                GongName = d.ProjectName,//设计清单工程名称
                Jiwei = d.StandsNums,//设计清单机位数量
                GongAddress = d.ProjectAddress,//设计清单工程地址
                JiatiColor = d.Color,//设计清单架体颜色·
                Yewu = d.Salesman,//设计清单业务负责人
                Phone = d.Phone0,//业务负责人联系方式
                JinTime = d.InDate,//进场时间
                DesignListName = d.Name,
            };
            select = select.BuildExtendSelectExpre();
            var q = from a in GetIQueryable().AsExpandable()
                    join c in Db.GetIQueryable<PB_DeliveryNoticeItem>() on a.ParentId equals c.ParentId into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_DesignList>() on c.DesignId equals d.Id into cd
                    from d in cd.DefaultIfEmpty()
                    select @select.Invoke(a, c, d);
            PB_PickUpMainLoading listdata = new PB_PickUpMainLoading();
            var qdata = await q.Where(x => x.ParentId == ParentId && x.Id == id).FirstOrDefaultAsync();
            Expression<Func<PB_PickUpList, PB_Material, PB_StorArea, PB_Rack, PB_Location, PB_DesignDetail, Base_UnitGroup, PB_PickUpListLoading>> selects = (a, b, c, d, e, f, g) => new PB_PickUpListLoading
            {
                MaterialName = b.Name,//物料名称
                Spec = b.Spec,
                Weight = b.Weight,
                Nums = f.Nums,
                RealNums = f.RealNums,
                ShipmentNums = f.ShipmentNums,
                Danwei = g.Name,
            };
            selects = selects.BuildExtendSelectExpre();
            var k = from a in Db.GetIQueryable<PB_PickUpList>().AsExpandable()
                    join b in Db.GetIQueryable<PB_Material>() on a.MaterialId equals b.Id into ab
                    from b in ab.DefaultIfEmpty()
                    join c in Db.GetIQueryable<PB_StorArea>() on a.AreaId equals c.Id into ac
                    from c in ac.DefaultIfEmpty()
                    join d in Db.GetIQueryable<PB_Rack>() on a.RackId equals d.Id into ad
                    from d in ad.DefaultIfEmpty()
                    join e in Db.GetIQueryable<PB_Location>() on a.LocationId equals e.Id into ae
                    from e in ae.DefaultIfEmpty()
                    join f in Db.GetIQueryable<PB_DesignDetail>() on a.SubId equals f.Id into af
                    from f in af.DefaultIfEmpty()
                    join g in Db.GetIQueryable<Base_UnitGroup>() on b.UnitGroupId equals g.Id into bg
                    from g in bg.DefaultIfEmpty()
                    select @selects.Invoke(a, b, c, d, e, f, g);
            k = k.Where(x => x.ParentId == qdata.Id);
            var res = await k.ToListAsync();
            PB_PickUpMainLoading pblist = new PB_PickUpMainLoading();
            pblist.GongName = qdata.GongName;
            pblist.Jiwei = qdata.Jiwei;
            pblist.GongAddress = qdata.GongAddress;
            pblist.JiatiColor = qdata.JiatiColor;
            pblist.Yewu = qdata.Yewu;
            pblist.Phone = qdata.Phone;
            pblist.JinTime = qdata.JinTime;
            pblist.Id = qdata.Id;
            pblist.CreateTime = qdata.CreateTime;
            pblist.Remarks = qdata.Remarks;
            pblist.CreatorId = qdata.CreatorId;
            pblist.Deleted = qdata.Deleted;
            pblist.StyleName = qdata.StyleName;
            pblist.DeliveryTime = qdata.DeliveryTime;
            pblist.CusID = qdata.CusID;
            pblist.CustomerAddress = qdata.CustomerAddress;
            pblist.Toexamine = qdata.Toexamine;
            pblist.Memo = qdata.Memo;
            pblist.ParentId = qdata.ParentId;
            pblist.BuildingId = qdata.BuildingId;
            pblist.QrCode = qdata.QrCode;
            pblist.Status = qdata.Status;
            pblist.ReviewerId = qdata.ReviewerId;
            pblist.ReviewerName = qdata.ReviewerName;
            pblist.ReviewTIme = qdata.ReviewTIme;
            pblist.CusName = qdata.CusName;
            pblist.DesignListName = qdata.DesignListName;
            pblist.SubData = res;
            if (res != null)
            {
                return pblist;
            }
            else
            {
                throw new BusException("暂无数据");
            }

        }

        public async Task<PB_DesignList> onProjectid(string ProjectId, string BuildingNumberID)
        {
            throw new NotImplementedException();
        }

        public async Task<List<StepListDto>> GetStepListId(string NoticeItemId)
        {
            var list = new List<StepListDto>();
            string sql = " SELECT  '第一步' AS 'title',CASE  WHEN IFNULL(SUM(OneStepReady),0) >= IFNULL(SUM(OneStep),0) THEN TRUE ELSE FALSE  END state,'OneStep' AS 'key' FROM ( SELECT d.OneStepReady,  d.OneStep, d.State,  d.Id AS SubId,   b.IsStandard FROM PB_DeliveryNoticeItem AS a  JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE a.Id = '" + NoticeItemId+ "' AND d.State = 0 ) AS t UNION ALL SELECT  '第二步' AS 'title',CASE WHEN IFNULL(SUM(TwoStepReady),0) >= IFNULL(SUM(TwoStep),0) THEN TRUE ELSE FALSE END state, 'TwoStep' AS 'key'FROM  (SELECT d.TwoStepReady, d.TwoStep,  d.State,d.Id AS SubId, b.IsStandard FROM PB_DeliveryNoticeItem AS a JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id WHERE  a.Id = '" + NoticeItemId+ "'   AND d.State = 0  ) AS o UNION ALL SELECT  '第三步' AS 'title', CASE    WHEN IFNULL(SUM(ThreeStepReady),0) >= IFNULL(SUM(ThreeStep),0) THEN TRUE ELSE FALSE END state,'ThreeStep' AS 'key' FROM( SELECT d.ThreeStepReady, d.ThreeStep,d.State, d.Id AS SubId,  b.IsStandard FROM PB_DeliveryNoticeItem AS a  JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE  a.Id = '" + NoticeItemId+ "'   AND d.State = 0  ) AS k  union all SELECT  '第四步' AS 'title',CASE  WHEN IFNULL(SUM(FourStepReady),0) >= IFNULL(SUM(FourStep),0) THEN TRUE ELSE FALSE  END state,  'FourStep' AS 'key' FROM  ( SELECT d.FourStepReady,d.FourStep, d.State,d.Id AS SubId,b.IsStandard FROM PB_DeliveryNoticeItem AS a JOIN PB_DesignDetail AS d ON a.DesignId=d.ParentId  	JOIN PB_Material AS b ON d.MaterialId = b.Id   ) AS k  union all SELECT    '第五步' AS 'title', CASE  WHEN IFNULL(SUM(FiveStepReady),0) >= IFNULL(SUM(FiveStep),0) THEN TRUE ELSE FALSE  END state,  'FiveStep' AS 'key' FROM(SELECT  d.FiveStepReady,  d.FiveStep,  d.State, d.Id AS SubId, b.IsStandard FROM  PB_DeliveryNoticeItem AS a   JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE a.Id = '" + NoticeItemId+ "' AND d.State = 0) AS k union all SELECT '第六步' AS 'title',CASE  WHEN IFNULL(SUM(SixStepReady),0) >= IFNULL(SUM(SixStep),0) THEN TRUE ELSE FALSE END state,  'SixStep' AS 'key' FROM  (  SELECT   d.SixStepReady,  d.SixStep, d.State, d.Id AS SubId,  b.IsStandard FROM PB_DeliveryNoticeItem AS a JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId JOIN PB_Material AS b ON d.MaterialId = b.Id WHERE a.Id = '" + NoticeItemId+ "' AND d.State = 0  ) AS k union all SELECT '第七步' AS 'title', CASE   WHEN IFNULL(SUM(SevenStepReady),0) >= IFNULL(SUM(SevenStep),0) THEN TRUE ELSE FALSE END state, 'SevenStep' AS 'key' FROM (  SELECT d.SevenStepReady,   d.SevenStep,  d.State,  d.Id AS SubId,  b.IsStandard  FROM  PB_DeliveryNoticeItem AS a  JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE  a.Id = '" + NoticeItemId+"' AND d.State = 0  ) AS k  union all SELECT  '第八步' AS 'title', CASE   WHEN IFNULL(SUM(EightStep),0) >= IFNULL(SUM(EightStep),0) THEN TRUE ELSE FALSE  END state, 'EightStep' AS 'key' FROM  (  SELECT     d.EightStepReady,  d.EightStep,  d.State,   d.Id AS SubId,   b.IsStandard  FROM  PB_DeliveryNoticeItem AS a   JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE   a.Id = '"+NoticeItemId+"'     AND d.State = 0 ) AS k    union all SELECT   '第九步' AS 'title', CASE   WHEN IFNULL(SUM(TenStepReady),0) >= IFNULL(SUM(TenStep),0) THEN TRUE ELSE FALSE END state, 'TenStep' AS 'key' FROM ( SELECT d.TenStepReady,  d.TenStep,   d.State,  d.Id AS SubId,   b.IsStandard FROM  PB_DeliveryNoticeItem AS a  JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId JOIN PB_Material AS b ON d.MaterialId = b.Id  WHERE a.Id = '"+NoticeItemId+"' AND d.State = 0 ) AS k  union all SELECT '第十步' AS 'title', CASE    WHEN IFNULL(SUM(ElevenStepReady),0) >= IFNULL(SUM(ElevenStep),0) THEN TRUE ELSE FALSE  END state,  'ElevenStep' AS 'key' FROM  ( SELECT  d.ElevenStepReady,    d.ElevenStep,   d.State,   d.Id AS SubId,    b.IsStandard  FROM   PB_DeliveryNoticeItem AS a  JOIN PB_DesignDetail AS d ON a.DesignId = d.ParentId  JOIN PB_Material AS b ON d.MaterialId = b.Id WHERE  a.Id = '"+NoticeItemId+"'    AND d.State = 0 ) AS k ";
            list =await Db.GetListBySqlAsync<StepListDto>(sql.ToString());
            return list;


        }

        //原始替换物料保存接口
        [DataAddLog(UserLogType.备货管理, "Id", "备货管理")]
        public async Task<AjaxResult> AddStepsPickMain(AddStepsPickMain data)
        {
          

            string sqllist = "SELECT a.Id,a.Remarks FROM PB_DesignDetail as a LEFT JOIN PB_DesignList as b  on a.ParentId = b.Id LEFT JOIN PB_DeliveryNoticeItem AS c ON c.DesignId = b.Id WHERE c.Id = '" + data.ParentId + "'";
            List<PB_DesignDetail> list = await Db.GetListBySqlAsync<PB_DesignDetail>(sqllist);
            string sql = String.Empty;
            AjaxResult falseReturn = new AjaxResult
            {
                Success = false,
                Msg = "失败请重新刷新库存！库存量不足"
            };
            List<childrenList> childrenLists = new List<childrenList>(); 
            //List<StepsList> SubData = data.SubData;
            //foreach (var item in SubData) {
            //    foreach (var items in item.childrenList) {
            //        childrenLists.Add(items);
            //    }
            //}

            ////获取传递进来的货位物料应备总数
            //var GroupLoctionList = childrenLists.GroupBy(x => new { x.LocationId, x.MaterialId, }).Select(x => new { LocationId = x.Key.LocationId, MaterialId = x.Key.MaterialId, StockVolume = x.Sum(n => n.StockVolume) }).ToList();

            ////获取可用库存数据
            //var LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().ToListAsync();
            //if (GroupLoctionList.Count > 0)
            //{
            //    for (int i = 0; i < GroupLoctionList.Count; i++)
            //    {
                   
            //            var LocalMaterialInfo = LocalMaterial.Where(P => P.LocalId == GroupLoctionList[i].LocationId && P.MaterialId == GroupLoctionList[i].MaterialId).FirstOrDefault();
            //            if (LocalMaterialInfo == null)
            //            {
            //                return falseReturn;
            //            }
            //            if ((LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R) < GroupLoctionList[i].StockVolume)
            //            {
            //            double TotalInventory = (LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R);
                       
                       
            //            var PB_Location =await Db.GetIQueryable<PB_Location>().Where(m => m.Id == GroupLoctionList[i].LocationId).FirstOrDefaultAsync();
            //            var PB_Rack = await Db.GetIQueryable<PB_Rack>().Where(m => m.Id == PB_Location.RackId).FirstOrDefaultAsync();
            //            var PB_StorArea = await Db.GetIQueryable<PB_Rack>().Where(m => m.Id == PB_Location.AreaId).FirstOrDefaultAsync();
            //            var PB_Materail =await Db.GetIQueryable<PB_Material>().Where(m=>m.Id==GroupLoctionList[i].MaterialId).FirstOrDefaultAsync();
            //            throw new BusException($"{PB_StorArea.Name}{PB_Rack.Name}{PB_Location.Name}{PB_Materail.Name} 库存量不足 库存总数{TotalInventory}");
                       
            //            }
            //        }
               
            //}
            //创建备货单主表
            PB_PickUpMain pB_PickUpMain = new PB_PickUpMain();
            //创建备货与分布对应关系表
            List<PB_PickListSteps> pB_PickListSteplist = new List<PB_PickListSteps>();
            List<PB_PickUpList> pB_PickUpLists1 = new List<PB_PickUpList>();
            string id = IdHelper.GetId();
            pB_PickUpMain.Id = id;
            pB_PickUpMain.QrCode = "21," + id;
            pB_PickUpMain.CreateTime = DateTime.Now;
            pB_PickUpMain.CreatorId = _operator.UserId;
            pB_PickUpMain.Deleted = false;
            pB_PickUpMain.OrderType = 0;
            pB_PickUpMain.Memo = data.Memo;
            pB_PickUpMain.StyleName = data.StyleName;
            pB_PickUpMain.DeliveryTime = data.DeliveryTime;
            pB_PickUpMain.CusID = data.CusID;
            pB_PickUpMain.CusName = data.CusName;
            pB_PickUpMain.CustomerAddress = data.CustomerAddress;
            pB_PickUpMain.Toexamine = "待审核";
            pB_PickUpMain.Status = 0;
            var q = await Db.GetIQueryable<PB_DeliveryNoticeItem>().Where(s => s.Id == data.ParentId).FirstOrDefaultAsync();
            var p = await Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == q.DesignId).FirstOrDefaultAsync();
            pB_PickUpMain.BuildingId = data.BuildingId;
            pB_PickUpMain.ParentId = data.ParentId;
            pB_PickUpMain.BuildingNum = p.BuildingNumberID;
            pB_PickUpMain.ProjectId = p.ProjectId;
            pB_PickUpMain.AssociatedDelivery = data.AssociatedDelivery;
            pB_PickUpMain.UpdateTime = DateTime.Now;
            await Db.InsertAsync<PB_PickUpMain>(pB_PickUpMain);
            string steptitle = "";
            string Updatesql = "";
            for (int steps = 0; steps < data.SubData.Count; steps++)
            {
                for (int items = 0; items < data.SubData[steps].childrenList.Count; items++)
                {
                    PB_PickListSteps pB_PickListSteps = new PB_PickListSteps();
                    string itemid = IdHelper.GetId();
                    pB_PickListSteps.Id = itemid;
                    pB_PickListSteps.ParentId = id;
                    pB_PickListSteps.AreaId = data.SubData[steps].childrenList[items].AreaId;
                    pB_PickListSteps.RackId = data.SubData[steps].childrenList[items].RackId;
                    pB_PickListSteps.LocationId = data.SubData[steps].childrenList[items].LocationId;
                    pB_PickListSteps.StockVolume = data.SubData[steps].childrenList[items].StockVolume;
                    pB_PickListSteps.ReadyNums = 0;
                    pB_PickListSteps.SubId = data.SubData[steps].childrenList[items].PB_DesignListInfo;
                    pB_PickListSteps.OriginalMaterialId = data.SubData[steps].childrenList[items].OriginalMaterialId;
                    pB_PickListSteps.MaterialId = data.SubData[steps].childrenList[items].MaterialId;
                    pB_PickListSteps.Remarks = data.SubData[steps].childrenList[items].Remarks;
                    pB_PickListSteps.CreateTime = DateTime.Now;
                    pB_PickListSteps.CreatorId = _operator.UserId;
                    pB_PickListSteps.State = 0;
                    pB_PickListSteps.Deleted = false;
                    pB_PickListSteps.StepIndex = data.SubData[steps].key;
                    pB_PickListSteps.Slot = items;
                    if (!string.IsNullOrEmpty(data.SubData[steps].childrenList[items].StockVolume.ToString()))
                    {
                        Updatesql += "UPDATE IT_LocalMaterial SET Nums_R = IFNULL(Nums_R,0) +'" + data.SubData[steps].childrenList[items].StockVolume + "' WHERE LocalId='" + data.SubData[steps].childrenList[items].LocationId + "' AND MaterialId='" + data.SubData[steps].childrenList[items].MaterialId + "'; ";
                    }
                    pB_PickListSteplist.Add(pB_PickListSteps);
                    //设计清单拼装已备货数量
                    steptitle = data.SubData[steps].key + "Ready";
                    //反填设计清单已备数量
                    Updatesql += "update  PB_DesignDetail set " + steptitle + "= IFNULL(" + steptitle + ", 0)+" + data.SubData[steps].childrenList[items].StockVolume + " where MaterialId='" + data.SubData[steps].childrenList[items].MaterialId + "' AND Id='" + data.SubData[steps].childrenList[items].PB_DesignListInfo + "' ; ";
                   
                }
            }


            if (pB_PickListSteplist?.Count > 0)
            {
             
                //获取分步同group 获取同一货位的物料总和
                var PickListStepGroupList = pB_PickListSteplist.GroupBy(x => new { x.AreaId, x.RackId, x.LocationId, x.MaterialId, x.SubId }).Select(x => new { AreaId = x.Key.AreaId, RackId = x.Key.RackId, LocationId = x.Key.LocationId, MaterialId = x.Key.MaterialId, SubId = x.Key.SubId, StockVolume = x.Sum(n => n.StockVolume) });
                foreach (var item in PickListStepGroupList)
                {
                    //创建备货单
                    PB_PickUpList pB_PickUpList = new PB_PickUpList();
                    pB_PickUpList.Id = IdHelper.GetId();
                    pB_PickUpList.ParentId = id;
                    pB_PickUpList.AreaId = item.AreaId;
                    pB_PickUpList.RackId = item.RackId;
                    pB_PickUpList.MaterialId = item.MaterialId;
                    pB_PickUpList.LocationId = item.LocationId;
                    pB_PickUpList.StockVolume = item.StockVolume;
                    pB_PickUpList.ReadyNums = 0;
                    pB_PickUpList.SubId = item.SubId;
                    pB_PickUpList.State = 0;
                    pB_PickUpList.CreateTime = DateTime.Now;
                    pB_PickUpList.Remarks = list.FirstOrDefault(m=>m.Id== pB_PickUpList.SubId)==null?"":list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId).Remarks;
                    pB_PickUpList.CreatorId = _operator.UserId;
                    pB_PickUpList.Deleted = false;
                    pB_PickUpList.ShippedNums = 0;
                    pB_PickUpList.OddNumber = "";
                    pB_PickUpList.ShippingId = "";
                    pB_PickUpLists1.Add(pB_PickUpList);

                }
                
            }
            
            
            if (!string.IsNullOrEmpty(Updatesql))
            {
                await ExecuteSqlAsync(Updatesql);
               
            }


            if (pB_PickUpLists1.Count > 0)
            {
                await Db.InsertAsync(pB_PickListSteplist);
                await Db.InsertAsync(pB_PickUpLists1);
            
            }
            AjaxResult res = new AjaxResult
            {
                Success = true,
                Msg = "请求成功！"
            };

            return res;
        }

        /// <summary>
        /// 替换物料新增
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        [DataAddLog(UserLogType.备货管理, "Id", "备货管理")]
        public async Task<AjaxResult> ReplaceAddPickMain(AddStepsPickMain data) {
           
            //获取设计清单明细
            string sqllist = "SELECT a.Id,a.Remarks FROM PB_DesignDetail as a LEFT JOIN PB_DesignList as b  on a.ParentId = b.Id LEFT JOIN PB_DeliveryNoticeItem AS c ON c.DesignId = b.Id WHERE c.Id = '" + data.ParentId + "'";
            List<PB_DesignDetail> list = await Db.GetListBySqlAsync<PB_DesignDetail>(sqllist);
            //获取子数据 
            List<childrenList> childrenLists = new List<childrenList>();
            PB_PickUpMain pB_PickUpMain = new PB_PickUpMain();
            List<PB_PickListSteps> pB_PickListSteplist = new List<PB_PickListSteps>();
            List<PB_PickUpList> pB_PickUpLists1 = new List<PB_PickUpList>();
            //创建主表信息
            string ParentId = IdHelper.GetId();
            pB_PickUpMain.Id = ParentId;
            pB_PickUpMain.QrCode = "21," + ParentId;
            pB_PickUpMain.CreateTime = DateTime.Now;
            pB_PickUpMain.CreatorId = _operator.UserId;
            pB_PickUpMain.Deleted = false;
            pB_PickUpMain.OrderType = 1;
            pB_PickUpMain.Memo = data.Memo;
            pB_PickUpMain.StyleName = data.StyleName;
            pB_PickUpMain.DeliveryTime = data.DeliveryTime;
            pB_PickUpMain.CusID = data.CusID;
            pB_PickUpMain.CusName = data.CusName;
            pB_PickUpMain.CustomerAddress = data.CustomerAddress;
            pB_PickUpMain.Toexamine = "待审核";
            pB_PickUpMain.Status = 0;
            var q = await Db.GetIQueryable<PB_DeliveryNoticeItem>().Where(s => s.Id == data.ParentId).FirstOrDefaultAsync();
            var p = await Db.GetIQueryable<PB_DesignList>().Where(s => s.Id == q.DesignId).FirstOrDefaultAsync();
            pB_PickUpMain.BuildingId = data.BuildingId;
            pB_PickUpMain.ParentId = data.ParentId;
            pB_PickUpMain.BuildingNum = p.BuildingNumberID;
            pB_PickUpMain.ProjectId = p.ProjectId;
            pB_PickUpMain.AssociatedDelivery = data.AssociatedDelivery;
            pB_PickUpMain.UpdateTime = DateTime.Now;
            await Db.InsertAsync<PB_PickUpMain>(pB_PickUpMain);
            string sqlmain = "";
            for (int steps = 0; steps < data.SubData.Count; steps++)
            {
                for (int items = 0; items < data.SubData[steps].childrenList.Count; items++)
                {
                    PB_PickListSteps pB_PickListSteps = new PB_PickListSteps();
                    string itemid = IdHelper.GetId();
                    pB_PickListSteps.Id = itemid;
                    pB_PickListSteps.ParentId = ParentId;
                    pB_PickListSteps.AreaId = data.SubData[steps].childrenList[items].AreaId;
                    pB_PickListSteps.RackId = data.SubData[steps].childrenList[items].RackId;
                    pB_PickListSteps.LocationId = data.SubData[steps].childrenList[items].LocationId;
                    pB_PickListSteps.StockVolume = data.SubData[steps].childrenList[items].StockVolume;
                    pB_PickListSteps.ReadyNums = 0;
                    pB_PickListSteps.SubId = data.SubData[steps].childrenList[items].PB_DesignListInfo;
                    pB_PickListSteps.MaterialId = data.SubData[steps].childrenList[items].MaterialId;
                    pB_PickListSteps.Remarks = data.SubData[steps].childrenList[items].Remarks;
                    pB_PickListSteps.OriginalMaterialId = data.SubData[steps].childrenList[items].OriginalMaterialId;
                    pB_PickListSteps.CreateTime = DateTime.Now;
                    pB_PickListSteps.CreatorId = _operator.UserId;
                    pB_PickListSteps.State = 0;
                    pB_PickListSteps.Deleted = false;
                    pB_PickListSteps.StepIndex = data.SubData[steps].key;
                    pB_PickListSteps.Slot = items;
                    if (!string.IsNullOrEmpty(data.SubData[steps].childrenList[items].StockVolume.ToString()))
                    {
                        sqlmain += "UPDATE IT_LocalMaterial SET Nums_R = IFNULL(Nums_R,0) +'" + data.SubData[steps].childrenList[items].StockVolume + "' WHERE LocalId='" + data.SubData[steps].childrenList[items].LocationId + "' AND MaterialId='" + data.SubData[steps].childrenList[items].MaterialId + "'; ";
                    }
                    pB_PickListSteplist.Add(pB_PickListSteps);
                }
            }

            if (pB_PickListSteplist?.Count > 0)
            {

                //获取分步同group 获取同一货位的物料总和
                var PickListStepGroupList = pB_PickListSteplist.GroupBy(x => new { x.AreaId, x.RackId, x.LocationId, x.MaterialId, x.SubId }).Select(x => new { AreaId = x.Key.AreaId, RackId = x.Key.RackId, LocationId = x.Key.LocationId, MaterialId = x.Key.MaterialId, SubId = x.Key.SubId, StockVolume = x.Sum(n => n.StockVolume) });
                foreach (var item in PickListStepGroupList)
                {
                    //创建备货单
                    PB_PickUpList pB_PickUpList = new PB_PickUpList();
                    pB_PickUpList.Id = IdHelper.GetId();
                    pB_PickUpList.ParentId = ParentId;
                    pB_PickUpList.AreaId = item.AreaId;
                    pB_PickUpList.RackId = item.RackId;
                    pB_PickUpList.MaterialId = item.MaterialId;
                    pB_PickUpList.LocationId = item.LocationId;
                    pB_PickUpList.StockVolume = item.StockVolume;
                    pB_PickUpList.ReadyNums = 0;
                    pB_PickUpList.SubId = item.SubId;
                    pB_PickUpList.State = 0;
                    pB_PickUpList.CreateTime = DateTime.Now;  
                        pB_PickUpList.Remarks = list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId) == null ? "" : list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId).Remarks;
                    pB_PickUpList.CreatorId = _operator.UserId;
                    pB_PickUpList.Deleted = false;
                    pB_PickUpList.ShippedNums = 0;
                    pB_PickUpList.OddNumber = "";
                    pB_PickUpList.ShippingId = "";
                    pB_PickUpLists1.Add(pB_PickUpList);
                }
            }
                await Db.ExecuteSqlAsync(sqlmain);
                await Db.InsertAsync(pB_PickListSteplist);
                await Db.InsertAsync(pB_PickUpLists1);

            AjaxResult res = new AjaxResult
            {
                Success = true,
                Msg = "请求成功！"
            };

            return res;
        }

        [DataEditLog(UserLogType.备货管理, "Id", "备货管理")]
        public async Task<AjaxResult> UpdateStepsPickMain(AddStepsPickMain data)
        {
            string sql = "SELECT a.Id,a.Remarks FROM PB_DesignDetail as a LEFT JOIN PB_DesignList as b  on a.ParentId = b.Id LEFT JOIN PB_DeliveryNoticeItem AS c ON c.DesignId = b.Id WHERE c.Id = '"+data.ParentId+"'";
            List<PB_DesignDetail> list =await Db.GetListBySqlAsync<PB_DesignDetail>(sql);
            //获取原有的备货单明细信息
            List<PB_PickListSteps> PickUpList = await Db.GetIQueryable<PB_PickListSteps>().Where(x => x.ParentId == data.Id).ToListAsync();
            string sqlMain = string.Empty;
            string sqlMains = string.Empty;
            string StepNumber = string.Empty;
            string stepsreduce = String.Empty;
            foreach (var item in PickUpList)
            {
                //获取步数列
                StepNumber = item.StepIndex + "Ready";
                sqlMain += @" update  IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " WHERE LocalId='" + item.LocationId + "' AND MaterialId='" + item.MaterialId + "'; ";
                //减少设计清单步数已备量
                 sqlMain += "update  PB_DesignDetail set " + StepNumber + "= IFNULL(" + StepNumber + ", 0)-" + item.StockVolume + " where MaterialId='" + item.MaterialId + "' AND Id='" + item.SubId + "' ;";
                
            }
            //清空库存占用
     
             sqlMains += " delete from PB_PickListSteps where ParentId='" + data.Id + "';";
           sqlMains+= " delete from PB_PickUpList where ParentId='" + data.Id + "';";
            await Db.ExecuteSqlAsync(sqlMains);
            string steptitle = "";
            List<PB_PickUpList> pB_PickUpLists = new List<PB_PickUpList>();
            List<PB_PickListSteps> pB_PickListSteplist = new List<PB_PickListSteps>();
            #region 组装数据
            string Updatesql = "";
            for (int i = 0; i < data.SubData.Count; i++)
            {
                for (int k = 0; k < data.SubData[i].childrenList.Count; k++)
                {
                    PB_PickListSteps pB_PickListSteps = new PB_PickListSteps();
                    pB_PickListSteps.Id = IdHelper.GetId();//详情id
                    pB_PickListSteps.ParentId = data.Id;//主表ID
                    pB_PickListSteps.AreaId = data.SubData[i].childrenList[k].AreaId;//货区ID
                    pB_PickListSteps.RackId = data.SubData[i].childrenList[k].RackId;//货架ID
                    pB_PickListSteps.LocationId = data.SubData[i].childrenList[k].LocationId;//货位ID
                    pB_PickListSteps.StockVolume = data.SubData[i].childrenList[k].StockVolume;// 备货单明细数量
                    pB_PickListSteps.MaterialId = data.SubData[i].childrenList[k].MaterialId;//物料id
                    pB_PickListSteps.Remarks = data.SubData[i].childrenList[k].Remarks;//备注
                    pB_PickListSteps.CreateTime = DateTime.Now;//创建时间
                    pB_PickListSteps.CreatorId = _operator?.UserId; //创建人
                    pB_PickListSteps.SubId = data.SubData[i].childrenList[k].PB_DesignListInfo;
                    pB_PickListSteps.ReadyNums = 0;
                    pB_PickListSteps.State = 0;
                    pB_PickListSteps.Deleted = false;//伪删除
                    pB_PickListSteps.StepIndex = data.SubData[i].key;
                    pB_PickListSteps.Slot = k;
                    if (!string.IsNullOrEmpty(data.SubData?[i].childrenList[k].StockVolume.ToString()))
                    {
                        sqlMain += @"UPDATE IT_LocalMaterial SET Nums_R = IFNULL(Nums_R,0) +'" + data.SubData[i].childrenList[k].StockVolume + "' WHERE LocalId='" + data.SubData[i].childrenList[k].LocationId + "' AND MaterialId='" + data.SubData[i].childrenList[k].MaterialId + "'; ";
                    }
                    pB_PickListSteplist.Add(pB_PickListSteps);
                    //设计清单拼装已备货数量
                    steptitle = data.SubData[i].key + "Ready";
                    //反填设计清单已备数量
                     sqlMain += "update  PB_DesignDetail set " + steptitle + "= IFNULL(" + steptitle + ", 0)+" + data.SubData[i].childrenList[k].StockVolume + " where MaterialId='" + data.SubData[i].childrenList[k].MaterialId + "' AND Id='" + data.SubData[i].childrenList[k].PB_DesignListInfo + "' ;";
                    
                }
            }
            #endregion


            if (pB_PickListSteplist.Count > 0)
            {
                
                var PickListStepGroupList = pB_PickListSteplist.GroupBy(x => new { x.AreaId, x.RackId, x.LocationId, x.MaterialId, x.SubId }).Select(x => new { AreaId = x.Key.AreaId, RackId = x.Key.RackId, LocationId = x.Key.LocationId, MaterialId = x.Key.MaterialId, SubId = x.Key.SubId, StockVolume = x.Sum(n => n.StockVolume) });
                foreach (var item in PickListStepGroupList)
                {
                    //创建备货单
                    PB_PickUpList pB_PickUpList = new PB_PickUpList();
                    pB_PickUpList.Id = IdHelper.GetId();
                    pB_PickUpList.ParentId = data.Id;
                    pB_PickUpList.AreaId = item.AreaId;
                    pB_PickUpList.RackId = item.RackId;
                    pB_PickUpList.MaterialId = item.MaterialId;
                    pB_PickUpList.LocationId = item.LocationId;
                    pB_PickUpList.StockVolume = item.StockVolume;
                    pB_PickUpList.ReadyNums = 0;
                    pB_PickUpList.SubId = item.SubId;
                    pB_PickUpList.State = 0;
                    pB_PickUpList.CreateTime = DateTime.Now;
                    pB_PickUpList.Remarks = pB_PickUpList.Remarks = list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId) == null ? "" : list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId).Remarks; ;
                    pB_PickUpList.CreatorId = _operator.UserId;
                    pB_PickUpList.Deleted = false;
                    pB_PickUpList.ShippedNums = 0;
                    pB_PickUpList.OddNumber = "";
                    pB_PickUpList.ShippingId = "";
                    pB_PickUpLists.Add(pB_PickUpList);

                }
                
            }
           
            if (pB_PickUpLists.Count > 0)
            {
                await Db.InsertAsync<PB_PickListSteps>(pB_PickListSteplist);
                await Db.InsertAsync(pB_PickUpLists);

            } 
            AjaxResult res = new AjaxResult
            {
                Success = true,
                Msg = "请求成功！"
            };

            return res;
        }

        [DataEditLog(UserLogType.备货管理, "Id", "备货管理")]
        public async Task<AjaxResult> UpdateReplacePickMain(AddStepsPickMain data)
        {
            string sql = "SELECT a.Id,a.Remarks FROM PB_DesignDetail as a LEFT JOIN PB_DesignList as b  on a.ParentId = b.Id LEFT JOIN PB_DeliveryNoticeItem AS c ON c.DesignId = b.Id WHERE c.Id = '" + data.ParentId + "'";
            List<PB_DesignDetail> list = await Db.GetListBySqlAsync<PB_DesignDetail>(sql);
            //获取原有的备货单明细信息
            List<PB_PickListSteps> PickUpList = await Db.GetIQueryable<PB_PickListSteps>().Where(x => x.ParentId == data.Id).ToListAsync();
            string sqlMain = string.Empty;
            string sqlMains = string.Empty;
            string StepNumber = string.Empty;
        
            foreach (var item in PickUpList)
            {
                sqlMain += " update  IT_LocalMaterial set Nums_R=IFNULL(Nums_R,0)-" + item.StockVolume + " WHERE LocalId='" + item.LocationId + "' AND MaterialId='" + item.MaterialId + "'; ";
            }
            //清空库存占用
            sqlMains+=" delete from PB_PickListSteps where ParentId='" + data.Id + "';";
            sqlMains+=" delete from PB_PickUpList where ParentId='" + data.Id + "';";
            await Db.ExecuteSqlAsync(sqlMains);
            List<PB_PickUpList> pB_PickUpLists = new List<PB_PickUpList>();
            List<PB_PickListSteps> pB_PickListSteplist = new List<PB_PickListSteps>();
            #region 组装数据
            for (int i = 0; i < data.SubData.Count; i++)
            {
                for (int k = 0; k < data.SubData[i].childrenList.Count; k++)
                {
                    PB_PickListSteps pB_PickListSteps = new PB_PickListSteps();
                    pB_PickListSteps.Id = IdHelper.GetId();//详情id
                    pB_PickListSteps.ParentId = data.Id;//主表ID
                    pB_PickListSteps.AreaId = data.SubData[i].childrenList[k].AreaId;//货区ID
                    pB_PickListSteps.RackId = data.SubData[i].childrenList[k].RackId;//货架ID
                    pB_PickListSteps.LocationId = data.SubData[i].childrenList[k].LocationId;//货位ID
                    pB_PickListSteps.StockVolume = data.SubData[i].childrenList[k].StockVolume;// 备货单明细数量
                    pB_PickListSteps.MaterialId = data.SubData[i].childrenList[k].MaterialId;//物料id
                    pB_PickListSteps.Remarks = data.SubData[i].childrenList[k].Remarks;//备注
                    pB_PickListSteps.CreateTime = DateTime.Now;//创建时间
                    pB_PickListSteps.CreatorId = _operator?.UserId; //创建人
                    pB_PickListSteps.SubId = data.SubData[i].childrenList[k].PB_DesignListInfo;
                    pB_PickListSteps.ReadyNums = 0;
                    pB_PickListSteps.State = 0;
                    pB_PickListSteps.Deleted = false;//伪删除
                    pB_PickListSteps.StepIndex = data.SubData[i].key;
                    pB_PickListSteps.Slot = k;
                    pB_PickListSteps.OriginalMaterialId = data.SubData[i].childrenList[k].OriginalMaterialId;// 备货单明细
                    pB_PickListSteps.StockVolume = data.SubData[i].childrenList[k].StockVolume;// 备货单明细
                    if (!string.IsNullOrEmpty(data.SubData?[i].childrenList[k].StockVolume.ToString()))
                    {
                        sqlMain += @"UPDATE IT_LocalMaterial SET Nums_R = IFNULL(Nums_R,0) +" + data.SubData[i].childrenList[k].StockVolume + " WHERE LocalId='" + data.SubData[i].childrenList[k].LocationId + "' AND MaterialId='" + data.SubData[i].childrenList[k].MaterialId + "'; ";
                    }
                    pB_PickListSteplist.Add(pB_PickListSteps);
                }
            }
            #endregion


            if (pB_PickListSteplist.Count > 0)
            {
               
                var PickListStepGroupList = pB_PickListSteplist.GroupBy(x => new { x.AreaId, x.RackId, x.LocationId, x.MaterialId, x.SubId }).Select(x => new { AreaId = x.Key.AreaId, RackId = x.Key.RackId, LocationId = x.Key.LocationId, MaterialId = x.Key.MaterialId, SubId = x.Key.SubId, StockVolume = x.Sum(n => n.StockVolume) });
                foreach (var item in PickListStepGroupList)
                {
                    //创建备货单
                    PB_PickUpList pB_PickUpList = new PB_PickUpList();
                    pB_PickUpList.Id = IdHelper.GetId();
                    pB_PickUpList.ParentId = data.Id;
                    pB_PickUpList.AreaId = item.AreaId;
                    pB_PickUpList.RackId = item.RackId;
                    pB_PickUpList.MaterialId = item.MaterialId;
                    pB_PickUpList.LocationId = item.LocationId;
                    pB_PickUpList.StockVolume = item.StockVolume;
                    pB_PickUpList.ReadyNums = 0;
                    pB_PickUpList.SubId = item.SubId;
                    pB_PickUpList.State = 0;
                    pB_PickUpList.CreateTime = DateTime.Now;
                        pB_PickUpList.Remarks = pB_PickUpList.Remarks = list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId) == null ? "" : list.FirstOrDefault(m => m.Id == pB_PickUpList.SubId).Remarks; ;
                    pB_PickUpList.CreatorId = _operator.UserId;
                    pB_PickUpList.Deleted = false;
                    pB_PickUpList.ShippedNums = 0;
                    pB_PickUpList.OddNumber = "";
                    pB_PickUpList.ShippingId = "";
                    pB_PickUpLists.Add(pB_PickUpList);
                }
               
            }

            if (pB_PickUpLists.Count > 0)
            {
                await Db.ExecuteSqlAsync(sqlMain);
                await Db.InsertAsync<PB_PickListSteps>(pB_PickListSteplist);
                await Db.InsertAsync(pB_PickUpLists);
            }
            AjaxResult res = new AjaxResult
            {
                Success = true,
                Msg = "请求成功！"
            };

            return res;
        }
        /// <summary>
        ///备货效验 验证备货单是否能进行备货
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public async Task<VerifyClose> TranslationStock(AddStepsPickMain data) {

            //创建实体
            VerifyClose verifyClose = new VerifyClose();
            //获取明细数据
            var pb_PickListSteps = await Db.GetIQueryable<PB_PickListSteps>().Where(m => m.ParentId == data.ParentId).ToListAsync();
            if (pb_PickListSteps.Count > 0)
            {
                //获取货位物料汇总应备数量
                List<ValidityLoction> validityLoctions = new List<ValidityLoction>();
                List<StepsList> stepsLists = new List<StepsList>();
                int abnormalcount = 0;
                //创建list集合
                //分组获取存储进来的数据
                var GroupvalidityLoctionst = validityLoctions.GroupBy(x => new { x.LoctionId, x.MaterailId,x.StepIndex }).Select(x => new { LoctionId = x.Key.LoctionId, MaterailId = x.Key.MaterailId, StepIndex = x.Key.StepIndex, StockVolume = x.Sum(n => n.StockVolume) });
                //获取库存可用数据
                var LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().ToListAsync();
                //获取传递进来的步数数组
                //var list = data.SubData.Where(m=>m.childrenList.Count>0);
                foreach (var item in data.SubData)
                {
                    //创建步级model
                    StepsList stepsList = new StepsList();
                    stepsList.key = item.key;
                    foreach (var childrenListValid in item.childrenList)
                    {
                        //获取第一个物料
                        //string PB_PickListSteps = null;/*await Db.GetIQueryable<PB_PickListSteps>().Where(m => m.ParentId == data.ParentId && m.MaterialId == childrenListValid.MaterialId && m.LocationId == childrenListValid.LocationId && m.StepIndex == item.key).FirstOrDefaultAsync()*/
                        var _tmp = pb_PickListSteps.FirstOrDefault(m => m.ParentId == data.ParentId && m.MaterialId == childrenListValid.MaterialId && m.LocationId == childrenListValid.LocationId && m.StepIndex == item.key);

                        //插入效验记录临时表
                        ValidityLoction validityLoction = new ValidityLoction();
                        validityLoction.LoctionId = childrenListValid.LocationId;
                        validityLoction.MaterailId = childrenListValid.MaterialId;
                        validityLoction.StockVolume = childrenListValid.StockVolume- (_tmp == null?0: _tmp.StockVolume);
                      
                        validityLoction.StepIndex = item.key;

                        var GroupvalidityNums = GroupvalidityLoctionst.Where(m => m.LoctionId == validityLoction.LoctionId && m.MaterailId == validityLoction.MaterailId).FirstOrDefault();
                          validityLoctions.Add(validityLoction);

                        var LocalMaterialInfo = LocalMaterial.Where(P => P.LocalId == validityLoction.LoctionId && P.MaterialId == validityLoction.MaterailId).FirstOrDefault();
                        double? nums = (LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R);

                        double? num = validityLoction.StockVolume + (GroupvalidityNums == null ? 0 : GroupvalidityNums.StockVolume);
                        if (LocalMaterialInfo == null)
                        {
                            childrenListValid.ValidityStatus = 1.ToString();
                            
                            abnormalcount++;
                        }
                        else
                        {
                            childrenListValid.ValidityStatus = 0.ToString();
                        }
                        if ((LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R) < num)
                        {
                           
                            childrenListValid.ValidityStatus = 1.ToString();
                            childrenListValid.StockNumber = nums;
                            abnormalcount++;
                        }
                        else
                        {
                            childrenListValid.ValidityStatus = 0.ToString();
                            childrenListValid.StockNumber = nums;
                        }
                    }
                    //集合赋值
                    stepsList.childrenList = item.childrenList;
                    //填充数据
                    stepsLists.Add(stepsList);
                }
                //验证是否能保存
                if (abnormalcount > 0)
                {
                    verifyClose.State = 1;
                }
                else
                {
                    verifyClose.State = 0;
                }
                //绑定步数数据
                verifyClose.Detail = stepsLists;
            }
            else {
                //获取货位物料汇总应备数量
                List<ValidityLoction> validityLoctions = new List<ValidityLoction>();
                List<StepsList> stepsLists = new List<StepsList>();
                int abnormalcount = 0;
                var list = validityLoctions;

                //分组获取存储进来的数据
                var GroupvalidityLoctionst = list.GroupBy(x => new { x.LoctionId, x.MaterailId, }).Select(x => new { LoctionId = x.Key.LoctionId, MaterailId = x.Key.MaterailId, StockVolume = x.Sum(n => n.StockVolume) });

                //获取库存可用数据
                var LocalMaterial = await Db.GetIQueryable<IT_LocalMaterial>().ToListAsync();
                //获取传递进来的数组
                foreach (var item in data.SubData)
                {
                   
                    StepsList stepsList = new StepsList();
                    stepsList.key = item.key;
                    foreach (var childrenListValid in item.childrenList)
                    {
                        //插入效验记录临时表
                        ValidityLoction validityLoction = new ValidityLoction();
                        validityLoction.LoctionId = childrenListValid.LocationId;
                        validityLoction.MaterailId = childrenListValid.MaterialId;
                        validityLoction.StockVolume = childrenListValid.StockVolume;
                        var GroupvalidityNums = GroupvalidityLoctionst.Where(m => m.LoctionId == validityLoction.LoctionId && m.MaterailId == validityLoction.MaterailId).FirstOrDefault();
                        validityLoctions.Add(validityLoction);

                        var LocalMaterialInfo = LocalMaterial.Where(P => P.LocalId == validityLoction.LoctionId && P.MaterialId == validityLoction.MaterailId).FirstOrDefault();
                        double nums = (LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R);

                        double? num = validityLoction.StockVolume + (GroupvalidityNums == null ? 0 : GroupvalidityNums.StockVolume);
                        if (LocalMaterialInfo == null)
                        {
                            childrenListValid.ValidityStatus = 1.ToString();
                            abnormalcount++;
                        }
                        else
                        {
                            childrenListValid.ValidityStatus = 0.ToString();
                        }
                        if ((LocalMaterialInfo.Nums_N + LocalMaterialInfo.Nums_O - LocalMaterialInfo.Nums_R) < num)
                        {
                            childrenListValid.ValidityStatus = 1.ToString();
                            childrenListValid.StockNumber = nums;
                            abnormalcount++;
                        }
                        else
                        {
                            childrenListValid.ValidityStatus = 0.ToString();
                            childrenListValid.StockNumber = nums;
                        }
                    }
                    //集合赋值
                    stepsList.childrenList = item.childrenList;
                    //填充数据
                    stepsLists.Add(stepsList);
                }
                //验证是否能保存
                if (abnormalcount > 0)
                {
                    verifyClose.State = 1;
                }
                else
                {
                    verifyClose.State = 0;
                }
                //绑定步数数据
                verifyClose.Detail = stepsLists;
            }

         
            //返回
            return verifyClose;


        }

        public async Task<PageResult<InventoryDetail>> GetInventoryDetail(PageInput<RetrievalSeach> input)
        {
            //创建sql
            StringBuilder stringBuilder = new StringBuilder();

            stringBuilder.AppendLine("SELECT");
            stringBuilder.AppendLine("b.Id MaterialId,");
            stringBuilder.AppendLine("b.`Name` MaterialName,");
            stringBuilder.AppendLine("b.`Code` MaterialCode,");
            stringBuilder.AppendLine("b.Spec MaterialSpec,");
            stringBuilder.AppendLine("b.ProductCode,");
            stringBuilder.AppendLine("a.LocalId AS LocationId,");
            stringBuilder.AppendLine("locition.Location StorAreaName,");
            stringBuilder.AppendLine("CASE");
            stringBuilder.AppendLine("WHEN ifnull(( `a`.`ScanNum` - `a`.Nums_R), 0 )< 0 THEN");
            stringBuilder.AppendLine("0 ELSE ifnull(( `a`.`ScanNum` - `a`.Nums_R ), 0 ) ");
            stringBuilder.AppendLine("END StockNumber,");
            stringBuilder.AppendLine("deparent.`Name` StorageName,");
            stringBuilder.AppendLine("a.Id AS `OnlyId`,");
            stringBuilder.AppendLine("IFNULL(b.Weight, 0) Weight,");
            stringBuilder.AppendLine("Base_UnitGroup.`Name`  groupName");
            stringBuilder.AppendLine("FROM");
            stringBuilder.AppendLine("IT_LocalMaterial AS a");
            stringBuilder.AppendLine("LEFT JOIN PB_Material AS b ON a.MaterialId = b.Id");
            stringBuilder.AppendLine("LEFT JOIN Base_Department AS deparent ON a.StorId = deparent.Id");
            stringBuilder.AppendLine("LEFT JOIN View_LocationInfo AS locition ON a.LocalId = locition.LocationId");
            stringBuilder.AppendLine("LEFT JOIN Base_UnitGroup ON b.UnitGroupId = Base_UnitGroup.Id");
            stringBuilder.AppendLine("WHERE");
            stringBuilder.AppendLine("b.UsageStatus = 1");
            stringBuilder.AppendLine("AND ifnull(( `a`.`ScanNum` - `a`.Nums_R ), 0 )> 0");
            if (!input.Search.StorId.IsNullOrEmpty())
            {
                stringBuilder.AppendLine("AND a.StorId='" + input.Search.StorId + "' ");
            }
            if (!input.Search.Matername.IsNullOrEmpty())
            {
                stringBuilder.AppendLine("AND (b.Name like '%" + input.Search.Matername + "%' OR  b.Code like '%" + input.Search.Matername + "%') ");
            }
            var res = await Db.GetListBySqlAsync<InventoryDetail>(stringBuilder.ToString());
            int pagecount;//总页数
            List<InventoryDetail> listpro_down = res.ToList();//一个要分页的，类型数据集合，存储的是所有的数据
            List<InventoryDetail> productes = new List<InventoryDetail>();//用来存储要展示的数据
            int pagees = listpro_down.Count;//所有数据的总条数
                                            //计算总页数
            if (pagees % input.PageRows == 0)
            {
                pagecount = pagees / input.PageRows;
            }
            else
            {
                pagecount = (pagees / input.PageRows) + 1;
            }
            productes = listpro_down.Skip((input.PageIndex - 1) * input.PageRows).Take(input.PageRows).ToList();//分页的重点
            return new PageResult<InventoryDetail> { Data = productes, Total = pagees };

        }
        #endregion

        #region 私有成员

        #endregion
    }
}